﻿using Mysoft.Map.Extensions.DAL;
using Mysoft.QuickCode.Common;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;

namespace Mysoft.QuickCode.DAL
{
    /// <summary>
    /// 2018年7月13日 使用gudusoft.gsqlparser重写
    /// </summary>
    public class DBPager
    {
        #region 基于SQL查询的翻页功能

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pager"></param>
        /// <param name="sqlText"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static Pager<T> GetPager<T>(Pager pager, string sqlText, object sqlParams)
            where T : class, new()
        {
            //查询和计算总数SQL
            MSSQLParser parser = new MSSQLParser(sqlText);
            string sqlCount = parser.GetSqlCount();
            string sqlPager = parser.GetSqlPager(pager.PageNow, pager.PageSize,
                string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy);

            //查询分页
            Pager<T> tp = new Pager<T>();
            tp.Total = CPQuery.From(sqlCount, sqlParams)
                   .ExecuteScalar<int>();
            tp.Rows = CPQuery.From(sqlPager, sqlParams)
                .ToList<T>();
            return tp;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pager"></param>
        /// <param name="sqlText"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static Pager<T> GetPager<T>(Pager pager, string sqlText, params SqlParameter[] sqlParams)
            where T : class, new()
        {
            //查询和计算总数SQL
            MSSQLParser parser = new MSSQLParser(sqlText);
            string sqlCount = parser.GetSqlCount();
            string sqlPager = parser.GetSqlPager(pager.PageNow, pager.PageSize,
                string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy);

            //查询分页
            Pager<T> tp = new Pager<T>();
            //这里会如果不用克隆，就会出现 SqlParameter已经被另外一个集合引用的异常。
            tp.Total = CPQuery.From(sqlCount, sqlParams.Select(m => (SqlParameter)((ICloneable)m).Clone()).ToArray())
                   .ExecuteScalar<int>();
            tp.Rows = CPQuery.From(sqlPager, sqlParams.Select(m => (SqlParameter)((ICloneable)m).Clone()).ToArray())
                  .ToList<T>();
            return tp;
        }


        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="sqlText"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static TablePager GetPager(Pager pager, string sqlText, object sqlParams)
        {
            //查询和计算总数SQL
            MSSQLParser parser = new MSSQLParser(sqlText);
            string sqlCount = parser.GetSqlCount();
            string sqlPager = parser.GetSqlPager(pager.PageNow, pager.PageSize,
                string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy);

            //查询分页
            TablePager tp = new TablePager();
            tp.Total = CPQuery.From(sqlCount, sqlParams)
                   .ExecuteScalar<int>();
            tp.Table = CPQuery.From(sqlPager, sqlParams)
                .FillDataTable();
            return tp;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="sqlText"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static TablePager GetPager(Pager pager, string sqlText, params SqlParameter[] sqlParams)
        {
            //查询和计算总数SQL
            MSSQLParser parser = new MSSQLParser(sqlText);
            string sqlCount = parser.GetSqlCount();
            string sqlPager = parser.GetSqlPager(pager.PageNow, pager.PageSize,
                string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy);

            //查询分页
            TablePager tp = new TablePager();
            //这里会如果不用克隆，就会出现 SqlParameter已经被另外一个集合引用的异常。
            tp.Total = CPQuery.From(sqlCount, sqlParams.Select(m => (SqlParameter)((ICloneable)m).Clone()).ToArray())
                   .ExecuteScalar<int>();
            tp.Table = CPQuery.From(sqlPager, sqlParams.Select(m => (SqlParameter)((ICloneable)m).Clone()).ToArray())
                  .FillDataTable();
            return tp;
        }

        #endregion
    }
}
